package edu.utsa.cs.contactmanager;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class ContactDatabaseHelper extends SQLiteOpenHelper {

    public static final int DATABASE_VERSION = 5;
    public static final String DATABASE_NAME = "contactManager";
    
    public static final String CONTACT_TABLE_NAME = "contacts";
    public static final String FIELD_CONTACT_ID = "contact_id";
    public static final String FIELD_CONTACT_FIRST_NAME = "first";
    public static final String FIELD_CONTACT_LAST_NAME = "last";
    public static final String FIELD_CONTACT_EMAIL = "email";
    public static final String FIELD_CONTACT_PICTURE = "picture";
    public static final String FIELD_CONTACT_BLACKLIST = "blacklist";
    public static final String[] allContactColumns = {FIELD_CONTACT_ID, FIELD_CONTACT_FIRST_NAME, FIELD_CONTACT_LAST_NAME, FIELD_CONTACT_EMAIL, FIELD_CONTACT_PICTURE, FIELD_CONTACT_BLACKLIST};
    
    public static final String PEOPLES_PHONES_TABLE_NAME = "peoples_phone";
    public static final String FIELD_PEOPLES_PHONES_UID = "ph_id";
    public static final String FIELD_CONTACT_PHONE_NUMBER = "phone";
    public static final String[] allPeoplesPhoneColumns = {FIELD_PEOPLES_PHONES_UID, FIELD_CONTACT_ID, FIELD_CONTACT_PHONE_NUMBER};
    
    public static final String GROUP_TABLE_NAME = "groups";
    public static final String FIELD_GROUP_ID = "group_id";
    public static final String FIELD_GROUP_NAME = "name";
    public static final String[] allGroupColumns = {FIELD_GROUP_ID, FIELD_GROUP_NAME};
    
    public static final String GROUPS_PEOPLE_TABLE_NAME = "groups_people";
    public static final String[] allGroupsPeopleColumns = {FIELD_GROUP_ID, FIELD_CONTACT_ID};
    
    private static final String CONTACT_TABLE_CREATE =
                "CREATE TABLE " + CONTACT_TABLE_NAME + " (" +
                FIELD_CONTACT_ID  + " integer primary key autoincrement, " +
                FIELD_CONTACT_FIRST_NAME + " TEXT, " +
                FIELD_CONTACT_LAST_NAME + " TEXT," + 
                FIELD_CONTACT_EMAIL + " TEXT," +
                FIELD_CONTACT_PICTURE + " TEXT," +
                FIELD_CONTACT_BLACKLIST + " INTEGER" +
                ");";
    
    private static final String PEOPLES_PHONE_CREATE = 
			"CREATE TABLE " + PEOPLES_PHONES_TABLE_NAME + " (" +
			FIELD_PEOPLES_PHONES_UID + " integer PRIMARY KEY autoincrement," +
			FIELD_CONTACT_ID + " integer, " +
			FIELD_CONTACT_PHONE_NUMBER + " TEXT, " +
			"FOREIGN KEY(" + FIELD_CONTACT_ID + ") REFERENCES " + CONTACT_TABLE_NAME + "(" + FIELD_CONTACT_ID + "));";
    
    private static final String GROUP_TABLE_CREATE = 
    			"CREATE TABLE " + GROUP_TABLE_NAME + " (" +
    			FIELD_GROUP_ID + " integer primary key autoincrement, " +
    			FIELD_GROUP_NAME + " TEXT" +
    			");";
    
    private static final String GROUPS_PEOPLE_CREATE = 
    			"CREATE TABLE " + GROUPS_PEOPLE_TABLE_NAME + " (" +
    			FIELD_GROUP_ID + " integer," +
    			FIELD_CONTACT_ID + " integer," +
    			"PRIMARY KEY(" + FIELD_GROUP_ID + ", " + FIELD_CONTACT_ID + "));";

    ContactDatabaseHelper(Context context){
        this(context, DATABASE_NAME);
    }
    
    ContactDatabaseHelper(Context context, String databaseLocation) {
        super(context, databaseLocation, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CONTACT_TABLE_CREATE);
        db.execSQL(PEOPLES_PHONE_CREATE);
        db.execSQL(GROUP_TABLE_CREATE);
        db.execSQL(GROUPS_PEOPLE_CREATE);
    }

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldV, int newV) {
		db.execSQL("DROP TABLE IF EXISTS " + PEOPLES_PHONES_TABLE_NAME);
		db.execSQL("DROP TABLE IF EXISTS " + GROUPS_PEOPLE_TABLE_NAME);
		db.execSQL("DROP TABLE IF EXISTS " + CONTACT_TABLE_NAME);
		db.execSQL("DROP TABLE IF EXISTS " + GROUP_TABLE_NAME);
		
	    onCreate(db);
		
	}
}